﻿using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using TCL.EO;
using TCL.FUNC;

namespace TCL.DB
{
    public class MSAccessDataProvider
    {
        #region "Properties"
        public virtual Boolean bWriteLog { get; set; }
        public virtual String sConnectionStrings { get; set; }
        #endregion

        #region DB Access Functions
        protected void SetConnection(ref OleDbCommand cmdMSAccess)
        {
            OleDbConnection connMSAccess = new OleDbConnection(ConfigurationManager.ConnectionStrings[sConnectionStrings].ConnectionString);
            if (connMSAccess.State != ConnectionState.Open)
            {
                connMSAccess.Open();
            }
            cmdMSAccess.Connection = connMSAccess;
        }

        protected void Convert_SqlCommand_OleDbCommand(SqlCommand cmdSql, ref OleDbCommand cmdMSAccess)
        {
            cmdMSAccess.CommandText = cmdSql.CommandText;
            cmdMSAccess.CommandType = cmdSql.CommandType;
            foreach (SqlParameter item in cmdSql.Parameters)
            {
                cmdMSAccess.Parameters.Add(new OleDbParameter(item.ParameterName, item.Value));
            }
            SetConnection(ref cmdMSAccess);
        }

        public DataSet GetDataSet(SqlCommand cmdSql)
        {
            OleDbCommand cmdMSAccess = new OleDbCommand();
            LogEO objLog = new LogEO(cmdSql, sConnectionStrings, ConstEx.Log_iType.DATABASE_SELECT);
            try
            {
                Convert_SqlCommand_OleDbCommand(cmdSql, ref cmdMSAccess);
                using (DataSet ds = new DataSet())
                {
                    using (OleDbDataAdapter da = new OleDbDataAdapter())
                    {
                        da.SelectCommand = cmdMSAccess;
                        da.Fill(ds);
                        objLog.bStatus = true;
                        return ds;
                    }
                }
            }
            finally
            {
                if (cmdMSAccess.Connection.State == ConnectionState.Open)
                    cmdMSAccess.Connection.Close();
                cmdMSAccess.Dispose();
                if (bWriteLog)
                    (new Logs()).WriteLog_To_DB(objLog, true);
            }
        }

        public DataTable GetDataTable(SqlCommand cmdSql)
        {
            return GetDataSet(cmdSql).Tables[0];
        }

        public OleDbDataReader GetReadOnlyData(SqlCommand cmdSql)
        {
            OleDbCommand cmdMSAccess = new OleDbCommand();
            LogEO objLog = new LogEO(cmdSql, sConnectionStrings, ConstEx.Log_iType.DATABASE_OTHER);
            try
            {
                Convert_SqlCommand_OleDbCommand(cmdSql, ref cmdMSAccess);
                objLog.bStatus = true;
                return cmdMSAccess.ExecuteReader();
            }
            finally
            {
                if (cmdMSAccess.Connection.State == ConnectionState.Open)
                    cmdMSAccess.Connection.Close();
                cmdMSAccess.Dispose();
                if (bWriteLog)
                    (new Logs()).WriteLog_To_DB(objLog, true);
            }
        }

        public void ExecuteNonQuery(SqlCommand cmdSql)
        {
            OleDbCommand cmdMSAccess = new OleDbCommand();
            LogEO objLog = new LogEO(cmdSql, sConnectionStrings, ConstEx.Log_iType.DATABASE_INSERT_UPDATE_DELETE);
            try
            {
                Convert_SqlCommand_OleDbCommand(cmdSql, ref cmdMSAccess);
                cmdMSAccess.ExecuteNonQuery();
                objLog.bStatus = true;
            }
            finally
            {
                if (cmdMSAccess.Connection.State == ConnectionState.Open)
                    cmdMSAccess.Connection.Close();
                cmdMSAccess.Dispose();
                if (bWriteLog)
                    (new Logs()).WriteLog_To_DB(objLog, true);
            }
        }

        public object ExecuteScalar(SqlCommand cmdSql)
        {
            OleDbCommand cmdMSAccess = new OleDbCommand();
            LogEO objLog = new LogEO(cmdSql, sConnectionStrings, ConstEx.Log_iType.DATABASE_OTHER);
            try
            {
                Convert_SqlCommand_OleDbCommand(cmdSql, ref cmdMSAccess);
                objLog.bStatus = true;
                return cmdMSAccess.ExecuteScalar();
            }
            finally
            {
                if (cmdMSAccess.Connection.State == ConnectionState.Open)
                    cmdMSAccess.Connection.Close();
                cmdMSAccess.Dispose();
                if (bWriteLog)
                    (new Logs()).WriteLog_To_DB(objLog, true);
            }
        }
        #endregion
    }
}


//public int DBSize()
//{
//    using (OleDbCommand cmd = new OleDbCommand("select sum(size) * 8 * 1024 from sysfiles"))
//    {
//        cmd.CommandType = CommandType.Text;
//        return (int)ExecuteScalar(cmd);
//    }
//}

//public DataTable getDatabaseSizeCurrent(String _DATABASE_NAME)
//{
//    OleDbCommand cmd = new OleDbCommand("getDatabaseSizeCurrent", connMSAccess);
//    cmd.CommandType = CommandType.StoredProcedure;
//    cmd.Parameters.Add(new SqlParameter("@DATABASE_NAME", _DATABASE_NAME));
//    return GetDataTable(cmd);
//}

//public bool CheckConnect()
//{
//    OleDbCommand cmd = new OleDbCommand("select getdate()");
//    if (DataTableEx.DataTable_OK(GetDataTable(cmd)))
//        return true;
//    else
//        return false;
//}

//public Boolean Backup_Database(String LinkBackup)
//{
//    OleDbCommand cmd = new OleDbCommand("BACKUP DATABASE [NguyenManhThang] TO DISK='" + LinkBackup + "'", connMSAccess);
//    ExecuteNonQuery(cmd);
//    return true;
//}

//public Boolean Restore_Database(String LinkRestore)
//{
//    OleDbCommand cmd = new OleDbCommand("USE MASTER RESTORE DATABASE [NguyenManhThang] FROM DISK='" + LinkRestore + "'", connMSAccess);
//    ExecuteNonQuery(cmd);
//    return true;
//}